Verifying that the master encryption key is encrypting the database

After configuring the master encryption key, you can verify that the master encryption key is encrypting the database, as expected.

To verify that the master encryption key is encrypting the database

1. Verify that the master encryption key is successfully encrypting table columns.

   1. Create a CUSTOMERS table in the database.

      sqlplus / as sysdba

      connect system/Temp1234

      SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY
      “<hsm_slot_password>”;
      SQL> CREATE TABLE CUSTOMERS (ID NUMBER(5), NAME VARCHAR(42), CREDIT_LIMIT NUMBER(10));
      

   2. Input values into the CUSTOMERS table.

      SQL> INSERT INTO CUSTOMERS VALUES (001, 'George Bailey', 10000);
      SQL> INSERT INTO CUSTOMERS VALUES (002, 'Denial Vettory', 20000);
      SQL> INSERT INTO CUSTOMERS VALUES (003, 'MS Dhoni', 30000);
      SQL> INSERT INTO CUSTOMERS VALUES (004, 'Shahid Afridi', 40000);
      

   3. Encrypt the CREDIT_LIMIT column of the CUSTOMERS table.

      SQL> ALTER TABLE CUSTOMERS MODIFY (CREDIT_LIMIT ENCRYPT);
      

   4. Return the values listed in the encrypted column in clear text.

      SQL> SELECT CREDIT_LIMIT FROM CUSTOMERS;
      

      Transparent data encryption decrypts the values automatically.

   5. List the encrypted columns in your database.

      SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;
      

   6. Finally, retrieve information about the hardware keystore.

      SQL> SELECT * FROM V$ENCRYPTION_WALLET;
      

2. Verify that the master encryption key is successfully encrypting tablespaces.

   1. Create an encrypted tablespace.

      SQL> CREATE TABLESPACE SECURESPACE DATAFILE
      '/u01/app/oracle/oradata/orcl/SECURE01.DBF' SIZE 150M ENCRYPTION DEFAULT STORAGE (ENCRYPT);
      

      OR

      SQL> CREATE TABLESPACE SECURESPACE DATAFILE
      ' C:\Oracle/oradata/orcl/SECURE01.DBF' SIZE 150M ENCRYPTION DEFAULT STORAGE (ENCRYPT);
      

   2. Create a table in the tablespace.

      SQL> CREATE TABLE EMPLOYEE (ID NUMBER(5),NAME VARCHAR(42),SALARY NUMBER(10)) TABLESPACE SECURESPACE;
      

   3. Insert some values in EMPLOYEE table.

      SQL> INSERT INTO EMPLOYEE VALUES (001,'JOHN SMITH',15000);
      SQL> INSERT INTO EMPLOYEE VALUES (002,'SCOTT TIGER',25000);
      SQL> INSERT INTO EMPLOYEE VALUES (003,'DIANA HAYDEN',35000);
      

   4. Display the contents of the EMPLOYEE table.

      SQL> SELECT * FROM EMPLOYEE;
      

   5. Close the keystore.

      SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY
      “<hsm_partition_password>”;
      

   6. Run the command to display the contents once again.

      SQL> SELECT * FROM EMPLOYEE;
      

      If the keystore is closed, you will get the following error:

      ERROR at line 1:
      ORA-28365: wallet is not open
      

      This error means you cannot list the contents of EMPLOYEE table.

   7. Open the keystore.

      SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY
      “<hsm_partition_password>”; SQL> exit